﻿using System;
using System.IO;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Threading;
using ExcelRefresh.Com;
using Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Core;
using Action = System.Action;
using MsoDocProperties = Microsoft.Office.Core.MsoDocProperties;

namespace ExcelRefresh.Excel
{
    public abstract class ExcelHelpers : ComHelpers
    {
        /// <summary>
        /// Opens an Excel workbook, and refreshes all data, including any Powerpivot cube.
        /// </summary>
        /// <param name="filePath">The absolute path to the Excel file to refresh</param>
        /// <remarks>
        /// If the workbook is already open by another user, this method will skip past it (behavior which might need rethinking).
        /// Otherwise, any embedded PowerPivot cube is detected and reprocessed, after which all data OLEDB and ODBC connections
        /// in the workbook are refreshed.
        /// </remarks>
        public static void RefreshAllWorkbookData(FileToRefresh file)
        {
            var startTime = DateTime.Now;
            UsingWorkbook(file.FilePath, book => ProcessEmbeddedCube(file, book));
            UsingWorkbook(file.FilePath, book => ProcessExcelWorkbook(file, book, startTime));
        }

        private static void ProcessEmbeddedCube(FileToRefresh file, Workbook book)
        {
            if (book.ReadOnly)
            {
                Trace.TraceWarning("Skipping file (open by another user) : {0}", file.FilePath);
                throw new FileInUseException("Excel file in use");
            }

            var startTime = DateTime.Now;
            Trace.TraceInformation("Opened for PowerPivot: {0}", book.Name);
            try
            {
                if (!RefreshPowerPivot(book, startTime))
                {
                    Trace.TraceInformation("Closed for PowerPivot: {0} (contains no cube)", book.Name);
                    return;
                }
                book.Comments = string.Format("Refreshed PowerPivot: {0:dd MMM, HH\\:mm} ", DateTime.Now);
                book.Save();
            }
            catch (Exception ex)
            {
                Trace.TraceInformation("PowerPivot Error in {0}  : {1}", file.FilePath, ex.Message);
                book.Comments = string.Format("PowerPivot Error: {0}", ex.Message);
                book.Save();
                throw;
            }
        }

        private static void ProcessExcelWorkbook(FileToRefresh file, Workbook book, DateTime startTime)
        {
            Trace.TraceInformation("Re-opened {0}", book.Name);
            try
            {
                ProcessAllRefreshables(book, startTime);
                var duration = DateTime.Now - startTime;
                Trace.TraceInformation("Refreshed {1}  - Time ellapsed: {0}", duration, file.FilePath);
                book.Comments = string.Format("Refreshed: {1:dd MMM, HH\\:mm}  Duration: {0:mm\\:ss}", duration, DateTime.Now);
                book.Save();
            }
            catch (Exception ex)
            {
                Trace.TraceError("{1} - {0}", ex.Message, file.FilePath);
                book.Comments = string.Format("Refresh Error: {0}", ex.Message);
                book.Save();
                return;
            }
            try
            {
                CreateArchive(book, file.BackupType);
                var copyToFolder = Settings.CopyRefreshedFilesToFolder;
                if (!string.IsNullOrEmpty(copyToFolder) && !file.RootPath.StartsWith(copyToFolder))
                {
                    var newPath = file.FilePath.Replace(file.RootPath, copyToFolder);
                    book.SaveAs(newPath);
                    CreateArchive(book, file.BackupType);
                    Trace.TraceInformation("Copied {0}", file.FilePath);
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError("Error copying/archiving {0}  : {1}", file.FilePath, ex.Message);
                throw;
            }
        }

        private static void CreateArchive(Workbook book, BackupStrategy strategy)
        {
            if (strategy == BackupStrategy.None)
                return;

            var filePath = book.FullName;
            var newName = filePath;

            switch (strategy)
            {
                case BackupStrategy.SameFolder:
                    newName = string.Format(
                            @"{0}\{1}.{3:yyyy\-MM\-dd HH\hmm}{2}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath),
                            Path.GetExtension(filePath),
                            DateTime.Now);
                    break;

                case BackupStrategy.SubFolder:
                    var folder = string.Format(
                            @"{0}\{1}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath));

                    Directory.CreateDirectory(folder);

                    newName = string.Format(
                            @"{0}\{1}\{1}.{3:yyyy\-MM\-dd HH\hmm}{2}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath),
                            Path.GetExtension(filePath),
                            DateTime.Now);
                    break;
            }

            var statusProp = book.BuiltinDocumentProperties["Content Status"];
            var previousContentStatus = statusProp.Value;
            statusProp.Value = string.Empty;
            book.SaveCopyAs(newName);
            statusProp.Value = previousContentStatus;
            File.SetAttributes(newName, File.GetAttributes(newName) | FileAttributes.ReadOnly | FileAttributes.Archive | FileAttributes.Compressed);
        }

        #region Powerpivot Refreshing

        public const int MaxParallelPowerPivotQueries = 3;

        private static string RefreshCubeCommand =
              "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" +
              "<Parallel MaxParallel=\"" + MaxParallelPowerPivotQueries.ToString() + "\">" +
              "<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" +
              "<Object><DatabaseID>{0}</DatabaseID></Object>" +
              "<Type>{1}</Type>" +
              "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" +
              "</Process>" +
              "</Parallel>" +
              "</Batch>";

        /// <summary>
        /// Connects to and refreshes any PowerPivot cube embedded in an Excel workbook
        /// </summary>
        /// <param name="book"></param>
        public static bool RefreshPowerPivot(Workbook book, DateTime startTime)
        {
            WorkbookConnection powerPivotConnection;
            int retryCount = 0;

            while (true)
            {
                try
                {
                    powerPivotConnection = book.Connections
                        .OfType<WorkbookConnection>()
                        .Where(n => n.Name == "PowerPivot Data" || n.Name == "ThisWorkbookDataModel")
                        .FirstOrDefault();

                    // Not a PowerPivot workbook? Then gracefully exit
                    if (powerPivotConnection == null)
                        return false;

                    powerPivotConnection.OLEDBConnection.Reconnect();
                    break;
                }
                catch (Exception ex)
                {
                    if (++retryCount < 3)
                        Thread.Sleep(new TimeSpan(0, 0, retryCount * 60));
                    else
                        throw new Exception("Cannot connect to PowerPivot Cube. " + ex.Message);
                }
            }

            UsingCOM(
                (ADODB.Connection)powerPivotConnection.OLEDBConnection.ADOConnection,
                ExecuteProcessFull
            );

            return true;
        }

        /// <summary>
        /// Sends a ProcessFull DMX command to an Analysis Services cube via an ADO connection
        /// </summary>
        /// <param name="connection"></param>
        private static void ExecuteProcessFull(ADODB.Connection connection)
        {
            const string qry = "select distinct object_id from $system.discover_object_activity where object_parent_path = 'Global.Sessions.SPID_{0}'";

            var spids = connection.RunQuery(
                "select session_spid from $system.discover_sessions",
                fields => string.Format(qry, fields[0].Value))
                .ToArray();

            var parts = spids
                .SelectMany(c => connection.RunQuery(c, fields => fields[0].Value.ToString().Split('.')))
                .FirstOrDefault(c => c.Length > 3 && c[0] == "Permissions" && c[2] == "Databases");

            if (parts == null)
                throw new Exception("Cannot retrieve database ID from PowerPivot cube");

            var databaseId = parts[3];
            object recordsAffected;

            var query = string.Format(RefreshCubeCommand, databaseId, "ProcessFull");
            connection.CommandTimeout = 40000;
            connection.Execute(query, out recordsAffected);

            Thread.Sleep(500);      // A quick snooze seems to help Excel Automation threads to catch up.
        }

        #endregion

        #region Excel Data Refreshing

        public class RefreshableProxy
        {
            public RefreshableProxy(OLEDBConnection connection, string name)
            {
                _refresh = () => connection.Refresh();
                _isRefreshing = () => connection.Refreshing;
                Name = "OleDB connection: " + name;
            }

            public RefreshableProxy(ODBCConnection connection, string name)
            {
                _refresh = () => connection.Refresh();
                _isRefreshing = () => connection.Refreshing;
                Name = "ODBC connection: " + name;
            }

            public RefreshableProxy(QueryTable queryTable, string name)
            {
                _refresh = () => queryTable.Refresh();
                _isRefreshing = () => queryTable.Refreshing;
                Name = "Query Table: " + name;
            }

            public RefreshableProxy(PivotTable pivotTable, string name)
            {
                var startTime = DateTime.Now.AddSeconds(-60);
                _refresh = () =>
                {
                    if (pivotTable.RefreshDate < startTime)
                        pivotTable.RefreshTable();
                    pivotTable.Update();
                };

                _isRefreshing = () => false;
                Name = name;
            }

            public RefreshableProxy(PivotCache cache, string name)
            {
                var startTime = DateTime.Now.AddSeconds(-60);
                _refresh = () =>
                {
                    try
                    {
                        if (cache.RefreshDate < startTime)
                            cache.Refresh();
                        cache.ResetTimer();
                    }
                    catch (Exception)
                    {   // don't throw, since this is usually an irrepairable and unimportant error
                        Trace.TraceWarning("{0} - {1}", cache, name);
                    }
                };
                _isRefreshing = () => false;
                Name = name;
            }

            private readonly Func<bool> _isRefreshing;
            private readonly Action _refresh;

            public void Refresh() { _refresh(); }
            public bool IsRefreshing { get { return _isRefreshing(); } }
            public string Name { get; set; }
        }

        public static RefreshableProxy[] GetRefreshableProxies(Workbook workbook)
        {
            var connections = workbook.Connections.Cast<WorkbookConnection>().ToArray();

            var OleDbRefreshes = from c in connections
                                 where c.Type == XlConnectionType.xlConnectionTypeOLEDB
                                 let oc = c.OLEDBConnection
                                 where oc.EnableRefresh
                                 select new RefreshableProxy(oc, c.Name);

            var OdbcRefreshes = from c in connections
                                where c.Type == XlConnectionType.xlConnectionTypeODBC
                                let oc = c.ODBCConnection
                                where oc.EnableRefresh
                                select new RefreshableProxy(oc, c.Name);

            var queryTables = from Worksheet sheet in workbook.Worksheets
                              from QueryTable queryTable in sheet.QueryTables
                              where queryTable.EnableRefresh
                                 && queryTable.QueryType != XlQueryType.xlOLEDBQuery
                                 && queryTable.QueryType != XlQueryType.xlODBCQuery
                              select new RefreshableProxy(queryTable, queryTable.Name);

            //var pivotCaches = from PivotCache cache in (PivotCaches)workbook.PivotCaches()
            //                  where cache.EnableRefresh
            //                  select new RefreshableProxy(cache,
            //                      string.Format("{0} - Pivot Cache {1}", workbook.FullName, cache.Index)
            //                      );

            var pivotTables = from Worksheet sheet in workbook.Worksheets
                              from PivotTable pivotTable in (PivotTables)sheet.PivotTables()
                              where !pivotTable.ManualUpdate
                              select new RefreshableProxy(pivotTable, pivotTable.Name + " on " + sheet.Name);

            return OdbcRefreshes
                .Concat(OleDbRefreshes)
                .Concat(queryTables)
                //.Concat(pivotCaches)
                .Concat(pivotTables)
                .ToArray();
        }

        public static void ProcessAllRefreshables(Workbook workbook, DateTime startTime)
        {
            var refreshables = GetRefreshableProxies(workbook);

            foreach (var refreshable in refreshables)
            {
                try
                {
                    var st = DateTime.Now;
                    refreshable.Refresh();
                    Trace.TraceInformation("Refreshed {0} on {1}. Duration: {2:mm\\:ss}", refreshable.Name, workbook.FullName, (DateTime.Now - st));
                    while (refreshable.IsRefreshing)
                        Thread.Sleep(250);

                }
                catch (Exception ex)
                {
                    throw new Exception(refreshable.Name + ": " + ex.Message);
                }
            }
        }

        #endregion
    }
}




